/**
 * 
 */
package org.istt.dao.hibernate;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.displaytag.properties.SortOrderEnum;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.type.StandardBasicTypes;
import org.istt.dao.WorkflowDao;
import org.istt.domain.ProcessStatus;
import org.istt.domain.Workflow;
import org.istt.domain.WorkflowProcess;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

/**
 * @author Huy
 *
 */
@Repository("workflowDao")
public class WorkflowDaoHibernate extends BaseDaoHibernate<Workflow, Long> implements
		WorkflowDao {
	@Autowired
	public WorkflowDaoHibernate(SessionFactory sessionFactory) {
		super(sessionFactory);
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.WorkflowDao#getWfpByWfId(java.lang.Long)
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getWfpByWfId(final Long workflowId) {
		return hibernateTemplate.execute(new HibernateCallback<List<Object[]>>() {
			
			@Override
			public List<Object[]> doInHibernate(Session session) throws HibernateException,
					SQLException {
				
				StringBuilder builder = new StringBuilder();
				builder.append(" SELECT {ps.*},(SELECT COUNT(*) FROM TBL_FILE_MANAGEMENT fm WHERE fm.WORKFLOW_PROCESS_ID = wfp.ID) AS used, ");
				builder.append(" {wfp.*}");
				builder.append(" FROM TBL_WORKFLOW wf JOIN TBL_WORKFLOW_PROCESS wfp ON wf.id = wfp.WORKFLOW_ID JOIN TBL_PROCESS_STATUS ps ON ps.ID = wfp.PROCESS_STATUS_ID ");
				builder.append(" WHERE wf.id = :id ");
				builder.append(" ORDER BY wfp.POSITION ");
				
				SQLQuery query = session.createSQLQuery(builder.toString())
										.addEntity("ps", ProcessStatus.class)
										.addScalar("used", StandardBasicTypes.INTEGER)
										.addEntity("wfp", WorkflowProcess.class);
				
				query.setLong("id", workflowId);
				return query.list();
			}
		});
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.WorkflowDao#removeWorkflowProcess(java.lang.Long, java.lang.Long)
	 */
	@Override
	public void removeWorkflowProcess(final Long workFlowId, final Long processStatusId) {
		String hsql = "DELETE WorkflowProcess wp WHERE wp.workflow.id = ? AND wp.processStatus.id = ?";
		hibernateTemplate.bulkUpdate(hsql, new Object[]{workFlowId, processStatusId});
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.WorkflowDao#getMaxPosition(java.lang.Long)
	 */
	@Override
	public Integer getMaxPosition(final Long workFlowId) {
		String hsql = "SELECT MAX(wp.position) FROM WorkflowProcess wp WHERE wp.workflow.id = ?";
		return (Integer)(hibernateTemplate.find(hsql, new Object[]{workFlowId}).get(0));
	}

	@Override
	public boolean isDeletable(Long workflowId) {
		String hsql = "select count(*) from fileMgr fm join fm.workflowProcess wp where wp.workflow.id = ?";
		Long num = (Long)(hibernateTemplate.find(hsql, new Object[]{workflowId}).get(0));
		return num == 0l;
	}

	@Override
	public Double getTotalDayOfWork(Long workflowId) {
		String hsql = "SELECT SUM(wp.processTime) FROM WorkflowProcess wp WHERE wp.workflow.id = ?";
		return (Double)(hibernateTemplate.find(hsql, new Object[]{workflowId}).get(0));
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Workflow> getWfByName(final String name,final int firstRecordIndex,
			final int pageSize,final SortOrderEnum sortDirection,final String sortCriterion) {
		// TODO Auto-generated method stub
		return (List<Workflow>) hibernateTemplate.execute(new HibernateCallback<Object>() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				Criteria criteria = session.createCriteria(Workflow.class);
				if(name!=null && !"".equals(name)){
					criteria.add(Restrictions.like("name","%" + name + "%"));
				}
				
				List<Workflow> result = criteria.setFirstResult(firstRecordIndex).setMaxResults(pageSize).list();
				return result;
			}
		});
	}

	@Override
	public int getWfRecordCountByName(final String name) {
		// TODO Auto-generated method stub
		return (Integer) hibernateTemplate.execute(new HibernateCallback<Object>() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				
				Criteria cri = session.createCriteria(Workflow.class);

				if (!StringUtils.isEmpty(name)) {
					cri.add(Restrictions.like("name","%" + name + "%"));
				}
				
				return ((Long)cri.setProjection(Projections.rowCount()).uniqueResult()).intValue();
			}
		});
	}
}
